﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class ConsignmentManager:ConnectionManager
    {
        #region Others
        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCONORDR WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Add/Update Order
        public SqlException AddOrder(ConsignmentUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO OCONORDR (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, Destination, StoreCode, CardCode, CardName, ContactPerson, PaymentTerms, Reference, BillTo, ShipTo, SalesMan, Remarks, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                                            " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @Destination, @StoreCode, @CardCode, @CardName, @ContactPerson, @PaymentTerms, @Reference, @BillTo, @ShipTo, @SalesMan, @Remarks, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount, GetDate(), @CreatedBy, GetDate(), @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@Destination", oUnit.Destination));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
                oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
                oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));



                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));



                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    string _NewGUID = System.Guid.NewGuid().ToString();
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO CONRDR1 (DocNum, ItemCode, ItemDesc, UoM, Qty, CQty, UnitPrice, PriceListCode, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuID) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @CQty, @UnitPrice, @PriceListCode, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuID)";

                    oCommand.Parameters.Add(new SqlParameter("@ItemGuID", _NewGUID));
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                    oCommand.ExecuteNonQuery();

                }
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateOrder(ConsignmentUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONORDR SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, Destination=@Destination, StoreCode=@StoreCode, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, PaymentTerms=@PaymentTerms, Reference=@Reference, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, DateModified=GetDate(), ModifiedBy=@ModifiedBy" +
                                                            " WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@Destination", oUnit.Destination));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));

                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE CONRDR1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {

                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        string _NewGUID = System.Guid.NewGuid().ToString();
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO CONRDR1 (DocNum, ItemCode, ItemDesc, UoM, Qty, CQty, PriceListCode, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @CQty, @PriceListCode, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";


                        oCommand.Parameters.Add(new SqlParameter("@ItemGuid", _NewGUID));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        if (oRow["Qty"] == DBNull.Value)
                        {
                            int _ZeroValue = 0;
                            oRow["Qty"] = _ZeroValue;
                        }
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();

                    }

                }

                return null;
            }
            catch(SqlException ex)
            {
                return ex;
            }

        }
        #endregion

        #region Post/Cancel/Close Order
        public SqlException PostOrder(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONORDR SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException CancelOrder(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONORDR SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally { oCommand.Dispose(); }
        }

        public SqlException CloseOrder(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONORDR SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Getting Data

        public DataTable GetPostedOrder()
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_CONSIGNMENT where DOCSTATUS='Posted'";

                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable GetPostedOrder(string _DocumentNumber)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_POSTED_CONSIGNMENT WHERE DOCNUM = @DOCNUM";
                oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DocumentNumber));

                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrdersByCustomer(string _CardName)
        {
            try
            {
                _CardName = _CardName.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 DocNum, Reference, PostingDate, CardCode, CardName, DocStatus FROM OCONORDR WHERE CardName LIKE @CardName ORDER BY DocNum DESC";
                oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrdersByCustRefPO(string _CustRefPO)
        {
            try
            {
                _CustRefPO = _CustRefPO.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 DocNum, Reference,PostingDate, CardCode, CardName, DocStatus FROM OCONORDR WHERE Reference LIKE @CustRefPO ORDER BY DocNum DESC";
                oCommand.Parameters.Add(new SqlParameter("@CustRefPO", _CustRefPO));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrders(string sDocumentNumber)
        {
            try
            {
                sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 DocNum, Reference,PostingDate, CardCode, CardName, DocStatus FROM OCONORDR WHERE DocNum LIKE @DOCNUM ORDER BY RecNo DESC";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrders(string _CardCode, string _DocNumber, string _DocumentStatus)
        {
            try
            {
                _DocNumber = '%' + _DocNumber.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM OCONORDR WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND DocStatus=@DocStatus ORDER BY PostingDate DESC";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOpenAndPostedOrders(string _CardCode, string _DocNumber)
        {
            try
            {
                _DocNumber = '%' + _DocNumber.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM OCONORDR WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND (DocStatus='Posted' Or DocStatus='Open') ORDER BY PostingDate DESC";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));


                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public string GetOrderNumber(string _Cardcode, string _DocNumber, string _DocumentStatus)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT DocNum FROM OCONORDR WHERE CardCode=@CardCode AND DocNum=@DOCNUM AND DocStatus=@DocStatus";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", _Cardcode));
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                if (ds.Tables[0].Rows.Count == 0)
                {
                    return "";
                }
                else
                {
                    DataRow oRow = ds.Tables[0].Rows[0];
                    return oRow["DocNum"].ToString();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataRow GetOrder(string sDocumentNumber)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM OCONORDR WHERE DocNum = @DOCNUM";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                DataRow oRow = ds.Tables[0].Rows[0];

                return oRow;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrderLineItems(string sDocumentNumber)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM CONRDR1 WHERE DocNum = @DOCNUM ORDER BY RECINDEX";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Report Retreivable

        public DataTable GetOrderStatus(DateTime _FROM, DateTime _TO)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.CommandText = "SP_CONSIGNMENT_STATUS";
                oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
                oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
